using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace Product
{

public class ProductLibs
{
    private string conectionString  = "";
    private SqlCommand command      = null;
    private SqlConnection conection = null;
    private SqlDataAdapter adapter  = null;

    public ProductLibs(string connStr)
    {
        conectionString = connStr;
    }

    public DataTable selectAllCountry()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_country";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAllProductType()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_productType";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAllRevenueUnit()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_currencyUnit ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAllRevenueCurrency()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_currency ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAllProductStatus()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_productStatus ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAlltm_disTemplate()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_disTemplate ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectAlltm_gradeLevel()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_gradeLevel ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public int getProductByProductCode(string productCode)
    {
        int rs = 0;
        try
        {
            DataTable dt = new DataTable();
            string sql = "SELECT productID " +
                            " FROM tm_product" +
                            " WHERE productCode = '" + productCode +"'";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            if (dt.Rows.Count > 0) rs = (int)dt.Rows[0]["productID"];

            conection.Close();
        }
        catch {}
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }

    public string getUserAccByUserId(int userId)
    {
        string rs = "";
        try
        {
            DataTable dt = new DataTable();
            string sql = "SELECT account " +
                            " FROM tm_users " +
                            " WHERE userId = " + userId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            rs = (string)dt.Rows[0]["account"];

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }

    public DataTable getProductInforTextByProductID(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select  * from tm_productInfo_text where productId = " + productID ;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        
        return dt;
    }

    public void DeleteProductInforText(string productInforId)
    {
        try
        {
            string sql = "Delete  tm_productInfo_text where id = " + productInforId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
     }

    public void InsertProductInforText(string productId, string inforCode, string inforText)
    {
        try
        {
            string sql = "INSERT INTO tm_productInfo_text(productId,infoCode,infoText) " +
                        " VALUES (" + productId + "," + inforCode + ",N'" + inforText + "')";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public DataTable getProductInforNumericByProductID(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select  * from tm_productInfo_numeric where productId = " + productID;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeleteProductInforNumeric(string productInforId)
    {
        try
        {
            string sql = "Delete  tm_productInfo_numeric where id = " + productInforId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void InsertProductInforNumeric(string productId, string inforCode, string inforNumeric)
    {
        try
        {
            string sql = "INSERT INTO tm_productInfo_numeric(productId,infoCode,infoValue) " +
                        " VALUES (" + productId + "," + inforCode + "," + inforNumeric + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public DataTable getLinhVuc(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql ="SELECT a.syscodeId,b.description1,b.code" +
                        " FROM tm_product_syscodes a RIGHT OUTER JOIN " +
                        " tm_productCat b ON a.syscodeId = b.syscodeId " +
                        " WHERE (a.productId = " + productID + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeleteLinhVuc(string Id)
    {
        try
        {
            string sql = "Delete  tm_product_syscodes where id = " + Id;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void AddCategory(string productId, string sysCodeId)
    {
        string sql = "INSERT INTO tm_product_syscodes(productId,syscodeId) " +
                    " VALUES (" + productId + "," + sysCodeId + ")";
        Common.sysLibs.ExecuteSQL(sql,conectionString);
    }

    public void DeleteCategory(string productId)
    {
        string sql ="DELETE tm_product_syscodes WHERE productId = " + productId ;
        Common.sysLibs.ExecuteSQL(sql,conectionString);
    }


    public DataTable getPhanLoai(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "SELECT tm_product_syscodes.syscodeId " +
                            " ,tm_productCat.description1 " +
                            " ,tm_product_syscodes.Id " +
                        " FROM tm_product_syscodes RIGHT OUTER JOIN " +
                        " tm_productCat ON tm_product_syscodes.syscodeId = tm_productCat.syscodeId " +
                        " WHERE (tm_product_syscodes.productId = " + productID + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeletePhanLoai(string Id)
    {
        try
        {
            string sql = "Delete  tm_product_syscodes where id = " + Id;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void InsertPhanLoai(string productId, string sysCodeId)
    {
        try
        {
            string sql = "INSERT INTO tm_product_syscodes(productId,syscodeId) " +
                        " VALUES (" + productId + "," + sysCodeId + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public DataTable getSanPhamTuongTu(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "SELECT tm_related_product.relatedProdId " +
                            " ,tm_related_product.id " +
                            " ,tm_product.Name1 " +
                            " ,tm_product.productId " +
                        " FROM tm_product INNER JOIN " +
                        " tm_related_product ON tm_product.productId = tm_related_product.relatedProdId " +
                        " WHERE tm_related_product.productId = " + productID;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeleteSanPhamTuongTu(string Id)
    {
        try
        {
            string sql = "Delete  tm_related_product where id = " + Id;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void InsertSanPhamTuongTu(string productId, string productRelatedID)
    {
        try
        {
            string sql = "INSERT INTO tm_related_product(productId,relatedProdId) " +
                        " VALUES (" + productId + "," + productRelatedID + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public bool productHasLogo(string productID, string fileType,ref string fileName)
    {
        bool rs = false;

        string tempFileName = "";
        try
        {
            string sql =    " SELECT FileName  " +
                            " FROM tm_product_file " +
                            " WHERE productId =" + productID +
                            " AND fileType = " + fileType;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            if (command.ExecuteScalar() != null)
                tempFileName = command.ExecuteScalar().ToString();

            conection.Close();

            rs = true;
        }
        catch 
        {
            throw;
        }
        fileName = tempFileName;
        return rs;
    }


    /*----------User for Product List and Seacrch Product -------*/

    #region temp
    //public DataTable selectProductsWithParameter(string FromDate, string ToDate,string Seller,string Keywork,string Name, string Description,string Category, string Status)
    //{
    //    DataTable dt = null;
    //    try
    //    {
    //        string sql = " SELECT tm_product.productId " +
    //                        " , tm_product.Name1 " +
    //                        " , tm_product.Description " +
    //                        " , tm_product.keywords " +
    //                        " , tm_product.RegisterDate " +
    //                        " , tm_product.ExpireDate " +
    //                        " , tm_product.status " +
    //                        " , tm_users.account " +
    //                        " , tm_productStatus.description1 " +
    //                    " FROM tm_product INNER JOIN " +
    //                    " tm_users ON tm_product.RegisterBy = tm_users.userId INNER JOIN " +
    //                    " tm_productStatus ON tm_product.status = tm_productStatus.statusCode ";

    //        if (Category != "")
    //        {
    //            sql += " INNER JOIN (SELECT tm_product_syscodes.productId " +
    //                    " ,tm_spccode.description1 " +
    //                    " FROM tm_product_syscodes RIGHT OUTER JOIN " +
    //                    " tm_spccode ON tm_product_syscodes.syscodeId = tm_spccode.id) A ON tm_product.productId = A.productId ";
    //        }

    //        SqlParameter paraFromDate;
    //        SqlParameter paraToDate;
    //        SqlParameter paraSeller;
    //        SqlParameter paraKeywork;
    //        SqlParameter paraName;
    //        SqlParameter paraDescription;
    //        SqlParameter paraCategory;
    //        SqlParameter paraStatus;

    //        dt = new DataTable();

    //        conection = new SqlConnection();
    //        conection.ConnectionString = conectionString;
    //        conection.Open();

    //        command = new SqlCommand();
    //        command.Connection = conection;

    //        if ( FromDate != "" || Seller != "" || Keywork !="" || Description != "" || Status !="" || Name !="" || Category !="")
    //        {
    //            sql += " WHERE ";
    //            if(FromDate != "" && ToDate != "")
    //            {
    //                sql += " tm_product.RegisterDate BETWEEN @FromDate AND @ToDate OR ";

    //                paraFromDate                = new SqlParameter();
    //                paraFromDate.DbType         = DbType.String;
    //                paraFromDate.ParameterName  = "@FromDate";
    //                paraFromDate.Value          = FromDate;

    //                paraToDate                  = new SqlParameter();
    //                paraToDate.DbType           = DbType.String;
    //                paraToDate.ParameterName    = "@ToDate";
    //                paraToDate.Value            = ToDate;

    //                command.Parameters.Add(paraFromDate);
    //                command.Parameters.Add(paraToDate);
    //            }

    //            if (Seller != "")
    //            {
    //                sql += " tm_users.fullName like N'%'+ @seller+ '%' OR ";

    //                paraSeller                  = new SqlParameter();
    //                paraSeller.DbType           = DbType.String;
    //                paraSeller.ParameterName    = "@seller";
    //                paraSeller.Value            = Seller;

    //                command.Parameters.Add(paraSeller);
    //            }

    //            if (Keywork != "")
    //            {
    //                sql += " tm_product.keywords like N'%'+ @keywork + '%' OR ";

    //                paraKeywork                 = new SqlParameter();
    //                paraKeywork.DbType          = DbType.String;
    //                paraKeywork.ParameterName   = "@keywork";
    //                paraKeywork.Value           = Keywork;

    //                command.Parameters.Add(paraKeywork);
    //            }

    //            if (Name != "")
    //            {
    //                sql += " tm_product.Name1 like N'%' + @Name + '%' OR ";

    //                paraName                = new SqlParameter();
    //                paraName.DbType         = DbType.String;
    //                paraName.ParameterName  = "@Name";
    //                paraName.Value          = Name;

    //                command.Parameters.Add(paraName);
    //            }

    //            if (Description != "")
    //            {
    //                sql += " tm_product.Description like N'%' + @Description + '%' OR ";

    //                paraDescription                 = new SqlParameter();
    //                paraDescription.DbType          = DbType.String;
    //                paraDescription.ParameterName   = "@Description";
    //                paraDescription.Value           = Description;

    //                command.Parameters.Add(paraDescription);
    //            }

    //            if (Category != "")
    //            {
    //                sql += " A.description1 like N'%' + @Category + '%' OR ";

    //                paraCategory                = new SqlParameter();
    //                paraCategory.DbType         = DbType.String;
    //                paraCategory.ParameterName  = "@Category";
    //                paraCategory.Value          = Category;

    //                command.Parameters.Add(paraCategory);
    //            }
                
    //            if (Status != "")
    //            {
    //                sql += " tm_productStatus.description1 like N'%' + @Status + '%' OR ";

    //                paraStatus                  = new SqlParameter();
    //                paraStatus.DbType           = DbType.String;
    //                paraStatus.ParameterName    = "@Status";
    //                paraStatus.Value            = Status;

    //                command.Parameters.Add(paraStatus);
    //            }

    //            sql = sql.Substring(0,sql.LastIndexOf("OR"));

    //        }

    //        command.CommandText = sql;


    //        adapter = new SqlDataAdapter();
    //        adapter.SelectCommand = command;
    //        adapter.Fill(dt);
    //    }
    //    catch (Exception exc)
    //    {
    //        throw exc;
    //    }
    //    finally
    //    {
    //        if (conection != null)
    //        {
    //            conection.Close();
    //            conection = null;
    //        }
    //        command = null;
    //        adapter = null;
    //    }
    //    return dt;
    //}

    //public DataTable selectProductWithParameter(string productCode, string Seller, string Status, string productType, string Category, string FromDate, string ToDate)
    //{
    //    DataTable dt = null;
    //    try
    //    {
    //        string sql = " SELECT tm_product.productId " +
    //                        " , tm_product.productCode " +
    //                        " , tm_product.Name1 " +
    //                        " , tm_product.Description " +
    //                        " , tm_product.keywords " +
    //                        " , tm_product.RegisterDate " +
    //                        " , tm_product.ExpireDate " +
    //                        " , tm_product.status " +
    //                        " , tm_users.account " +
    //                        " , tm_productStatus.description1 " +
    //                    " FROM tm_product INNER JOIN " +
    //                    " tm_users ON tm_product.RegisterBy = tm_users.userId INNER JOIN " +
    //                    " tm_productStatus ON tm_product.status = tm_productStatus.statusCode " +
    //                    " INNER JOIN tm_productType ON tm_product.productType = tm_productType.code ";

    //        if (Category != "")
    //        {
    //            sql += " INNER JOIN (SELECT tm_product_syscodes.productId " +
    //                    " ,tm_spccode.description1 " +
    //                    " FROM tm_product_syscodes RIGHT OUTER JOIN " +
    //                    " tm_spccode ON tm_product_syscodes.syscodeId = tm_spccode.syscodeId) A ON tm_product.productId = A.productId ";
    //        }


    //        SqlParameter paraProductCode;
    //        SqlParameter paraSeller;
    //        SqlParameter paraStatus;
    //        SqlParameter paraProductType;
    //        SqlParameter paraCategory;
    //        SqlParameter paraFromDate;
    //        SqlParameter paraToDate;

    //        dt = new DataTable();

    //        conection = new SqlConnection();
    //        conection.ConnectionString = conectionString;
    //        conection.Open();

    //        command = new SqlCommand();
    //        command.Connection = conection;

    //        if (productCode != "" || Seller != "" || Status != "" || productType != "" || Category != "" || FromDate != "" || ToDate != "")
    //        {
    //            sql += " WHERE ";

    //            if (productCode != "")
    //            {
    //                sql += " tm_product.productCode like N'%'+ @productCode + '%' OR ";

    //                paraProductCode = new SqlParameter();
    //                paraProductCode.DbType = DbType.String;
    //                paraProductCode.ParameterName = "@productCode";
    //                paraProductCode.Value = productCode;

    //                command.Parameters.Add(paraProductCode);
    //            }

    //            if (Seller != "")
    //            {
    //                sql += " tm_users.fullName like N'%'+ @seller+ '%' OR ";

    //                paraSeller = new SqlParameter();
    //                paraSeller.DbType = DbType.String;
    //                paraSeller.ParameterName = "@seller";
    //                paraSeller.Value = Seller;

    //                command.Parameters.Add(paraSeller);
    //            }

    //            if (Status != "")
    //            {
    //                sql += " tm_productStatus.description1 like N'%' + @Status + '%' OR ";

    //                paraStatus = new SqlParameter();
    //                paraStatus.DbType = DbType.String;
    //                paraStatus.ParameterName = "@Status";
    //                paraStatus.Value = Status;

    //                command.Parameters.Add(paraStatus);
    //            }


    //            if (productType != "")
    //            {
    //                sql += " tm_productType.description1 like N'%' + @productType + '%' OR ";

    //                paraProductType = new SqlParameter();
    //                paraProductType.DbType = DbType.String;
    //                paraProductType.ParameterName = "@productType";
    //                paraProductType.Value = productType;

    //                command.Parameters.Add(paraProductType);
    //            }

    //            if (Category != "")
    //            {
    //                sql += " A.description1 like N'%' + @Category + '%' OR ";

    //                paraCategory = new SqlParameter();
    //                paraCategory.DbType = DbType.String;
    //                paraCategory.ParameterName = "@Category";
    //                paraCategory.Value = Category;

    //                command.Parameters.Add(paraCategory);
    //            }


    //            if (FromDate != "" && ToDate != "")
    //            {
    //                sql += " tm_product.RegisterDate BETWEEN @FromDate AND @ToDate OR ";

    //                paraFromDate = new SqlParameter();
    //                paraFromDate.DbType = DbType.String;
    //                paraFromDate.ParameterName = "@FromDate";
    //                paraFromDate.Value = FromDate;

    //                paraToDate = new SqlParameter();
    //                paraToDate.DbType = DbType.String;
    //                paraToDate.ParameterName = "@ToDate";
    //                paraToDate.Value = ToDate;

    //                command.Parameters.Add(paraFromDate);
    //                command.Parameters.Add(paraToDate);
    //            }

    //            sql = sql.Substring(0, sql.LastIndexOf("OR"));

    //        }

    //        command.CommandText = sql;


    //        adapter = new SqlDataAdapter();
    //        adapter.SelectCommand = command;
    //        adapter.Fill(dt);
    //    }
    //    catch (Exception exc)
    //    {
    //        throw exc;
    //    }
    //    finally
    //    {
    //        if (conection != null)
    //        {
    //            conection.Close();
    //            conection = null;
    //        }
    //        command = null;
    //        adapter = null;
    //    }
    //    return dt;
    //}
    #endregion

    public DataTable selectProductWithParameter(string productCode, string Seller, string Status, string productType, string Category, string FromDate, string ToDate)
    {
        DataTable dt = null;
        try
        {
            string sql = " SELECT tm_product.productId " +
                            " , tm_product.productCode " +
                            " , tm_product.Name1 " +
                            " , tm_product.Description " +
                            " , tm_product.keywords " +
                            " , tm_product.RegisterDate " +
                            " , tm_product.ExpireDate " +
                            " , tm_product.status " +
                            " , tm_users.account " +
                            " , tm_users.fullName " +
                            " , tm_users.companyName1 " +
                            " , tm_users.userType " +
                            " , tm_productStatus.description1 " +
                        " FROM tm_product INNER JOIN " +
                        " tm_users ON tm_product.RegisterBy = tm_users.userId INNER JOIN " +
                        " tm_productStatus ON tm_product.status = tm_productStatus.statusCode " +
                        " INNER JOIN tm_productType ON tm_product.productType = tm_productType.code ";

            if (Category != "")
            {
                sql += " INNER JOIN (SELECT tm_product_syscodes.productId " +
                        " ,tm_spccode.description1 " +
                        " FROM tm_product_syscodes RIGHT OUTER JOIN " +
                        " tm_spccode ON tm_product_syscodes.syscodeId = tm_spccode.syscodeId) A ON tm_product.productId = A.productId ";
            }


            SqlParameter paraProductCode;
            SqlParameter paraSeller;
            SqlParameter paraStatus;
            SqlParameter paraProductType;
            SqlParameter paraCategory;
            SqlParameter paraFromDate;
            SqlParameter paraToDate;

            dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;

            if (productCode != "" || Seller != "" || Status != "" || productType != "" || Category != "" || FromDate != "" || ToDate != "")
            {
                sql += " WHERE ";

                if (productCode != "")
                {
                    sql += " tm_product.productCode like N'%'+ @productCode + '%' OR ";

                    paraProductCode = new SqlParameter();
                    paraProductCode.DbType = DbType.String;
                    paraProductCode.ParameterName = "@productCode";
                    paraProductCode.Value = productCode;

                    command.Parameters.Add(paraProductCode);
                }

                if (Seller != "")
                {
                    sql += " tm_users.fullName like N'%'+ @seller+ '%' OR ";

                    paraSeller = new SqlParameter();
                    paraSeller.DbType = DbType.String;
                    paraSeller.ParameterName = "@seller";
                    paraSeller.Value = Seller;

                    command.Parameters.Add(paraSeller);
                }

                if (Status != "")
                {
                    sql += " tm_productStatus.description1 like N'%' + @Status + '%' OR ";

                    paraStatus = new SqlParameter();
                    paraStatus.DbType = DbType.String;
                    paraStatus.ParameterName = "@Status";
                    paraStatus.Value = Status;

                    command.Parameters.Add(paraStatus);
                }


                if (productType != "")
                {
                    sql += " tm_productType.description1 like N'%' + @productType + '%' OR ";

                    paraProductType = new SqlParameter();
                    paraProductType.DbType = DbType.String;
                    paraProductType.ParameterName = "@productType";
                    paraProductType.Value = productType;

                    command.Parameters.Add(paraProductType);
                }

                if (Category != "")
                {
                    sql += " A.description1 like N'%' + @Category + '%' OR ";

                    paraCategory = new SqlParameter();
                    paraCategory.DbType = DbType.String;
                    paraCategory.ParameterName = "@Category";
                    paraCategory.Value = Category;

                    command.Parameters.Add(paraCategory);
                }


                if (FromDate != "" && ToDate != "")
                {
                    sql += " tm_product.RegisterDate BETWEEN @FromDate AND @ToDate OR ";

                    paraFromDate = new SqlParameter();
                    paraFromDate.DbType = DbType.String;
                    paraFromDate.ParameterName = "@FromDate";
                    paraFromDate.Value = FromDate;

                    paraToDate = new SqlParameter();
                    paraToDate.DbType = DbType.String;
                    paraToDate.ParameterName = "@ToDate";
                    paraToDate.Value = ToDate;

                    command.Parameters.Add(paraFromDate);
                    command.Parameters.Add(paraToDate);
                }

                sql = sql.Substring(0, sql.LastIndexOf("OR"));


                if (Status == "all" || productType == "all")
                {
                    sql = sql.Substring(0, sql.LastIndexOf("WHERE"));
                }
                

            }

            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }


    public string SQL_SelectProductBySysCodeID(string syscodeId)
    {
        return  "SELECT a.productId " +
                " , a.productCode " +
                " , a.Name1  " +
                " , a.RegisterDate " +
                " , a.ExpireDate  " +
                " , a.status  " +
                " , a.registerBy" +
                " , b.fullName " +
                " , c.displayCode" +
                " FROM tm_product a INNER JOIN  tm_users b ON a.RegisterBy = b.userId " +
                " INNER JOIN  tm_productStatus c ON a.status = c.statusCode  " +
                " INNER JOIN tm_product_syscodes d ON d.productId = a.productId " +
                " WHERE d.syscodeId = " + syscodeId +
                " AND  a.status = '" + Common.consts.constStatusENABLE + "'";
    }
    
    public DataTable selectProductWithSysCodeID(string syscodeId)
    {
        DataTable dt = null;
        try
        {
            string sql = SQL_SelectProductBySysCodeID(syscodeId);
            dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public void UpdateProductStatus(string productId, string status)
    {
        try
        {
            string sql = "UPDATE dbo.tm_product SET " +
                                " [status] = '" + status + "'" +
                            " WHERE " +
                            " [productId] = " + productId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void inserttm_user_profiles(string UserID, string infoType, string infoId, DateTime onDate)
    {
        try
        {
            string sql = " INSERT INTO tm_user_profiles(userId,infoType,infoId,onDate) " +
                            " VALUES (" + UserID + ",'" + infoType + "'," + infoId + ",'" + Common.sysLibs.ConvertToSQLDateString(onDate) + "') ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public bool isHasInMySpace(string UserID, string infoType, string infoId)
    {
        bool rs = false;
        try
        {
            string sql = " SELECT * " +
                        " FROM tm_user_profiles " +
                        " WHERE tm_user_profiles.userId = " + UserID +
                        " AND  tm_user_profiles.infoType = '" + infoType + "'" +
                        " AND  tm_user_profiles.infoId = " + infoId;


            DataTable dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            if (dt.Rows.Count > 0)
                rs = true;
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }


    public string SQL_TypicalProduct(int maxCount)
    {
        //BY NEWID() means randomly selected
        return "SELECT TOP " + maxCount.ToString() +
                " a.productId,a.Name1 AS SellerName,a.registerBy,a.RegisterDate,' ' AS displayCode" +
                " FROM dbo.tm_product a" +
                " WHERE (a.grade= '" + Common.consts.constTypicalGrade + "') " +
                " AND (a.status = '" + Common.consts.constStatusENABLE + "') " +
                " ORDER BY NEWID() ";
    }    
 
    public bool isProductOfUser(string UserID, string productID)
    {
        bool rs = false;
        try
        {
            string sql = "SELECT RegisterBy" +
                        " FROM tm_product " +
                        " WHERE tm_product.RegisterBy = " + UserID +
                        " AND  tm_product.productId = " + productID;


            DataTable dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            if (dt.Rows.Count > 0)
                rs = true;
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }
}
}